package com.javakc.mes.basis.mine.service;

import com.javakc.commonutils.jpa.base.service.BaseService;
import com.javakc.mes.basis.mine.dao.MineDao;
import com.javakc.mes.basis.mine.entity.MaintainMine;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.transaction.Transactional;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class MineService extends BaseService<MineDao, MaintainMine> {

    @PersistenceContext
    private EntityManager entityManager;


    public Page<MaintainMine> queryByPage(Integer pageNo, Integer pageSize) {
        Page<MaintainMine> page = dao.findAll(PageRequest.of(pageNo - 1, pageSize));
        return page;
    }

    @Transactional
    public void save(MaintainMine mine) {
        dao.save(mine);
    }

    public List<MaintainMine> queryAll() {
        return dao.findAll();
    }

    /**
     * 导出Excel
     *
     * @param response
     */
    public void exportExcel(HttpServletResponse response) {
        // ## 设置表头值
        String[] titles = {"矿井名称", "瓦斯等级", "设计能力", "投产日期", "改造能力", "改造日期","所属煤矿"};
        // ## 1.创建workbook,对应一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // ## 2.在workbook中创建一个sheet
        HSSFSheet hssfSheet = workbook.createSheet();
        // ## 3.在sheet中添加表头,第0行
        HSSFRow hssfRow = hssfSheet.createRow(0);
        // ## 4.在第0行设置表头数据
        for (int i = 0; i < titles.length; i++) {
            hssfRow.createCell(i).setCellValue(titles[i]);
        }

        // ## 5.查询并写入数据
        List<MaintainMine> mineList = dao.findAll();
        if (null != mineList) {
            for (int i = 0; i < mineList.size(); i++) {
                MaintainMine mine = mineList.get(i);
                // ## 创建行
                HSSFRow row = hssfSheet.createRow(i + 1);
                row.createCell(0).setCellValue(mine.getMineName());
                row.createCell(1).setCellValue(mine.getMineGasGrade());
                row.createCell(2).setCellValue(mine.getMineDesignPower());
                row.createCell(3).setCellValue(mine.getMineProductionTime());
                row.createCell(4).setCellValue(mine.getMineReformPower());
                row.createCell(5).setCellValue(mine.getMineReformTime());
                row.createCell(6).setCellValue(mine.getCollieryId());
            }
        }
        String fileName = new String(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));

        try {
            // ## 6.将Excel文件输出到客户端浏览器
            response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导入Excel
     *
     * @param file
     */
    @Transactional
    public void importExcel(MultipartFile file) {
        try {
            // ## 获取文件流
            InputStream inputStream = file.getInputStream();
            // ## 声明一个Excel接口
            Workbook workbook = null;
            // ## 根据文件后缀赋予不同POIExcel对象
            if (file.getOriginalFilename().endsWith(".xlsx")) {
                // ## 处理2007版及以上Excel
                workbook = new XSSFWorkbook(inputStream);
            } else {
                // ## 处理2003版本Excel
                workbook = new HSSFWorkbook(inputStream);
            }
            // ## 得到当前excel中的sheet总数
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                // ## 每循环一次,通过当前下标依次获取sheet
                Sheet sheet = workbook.getSheetAt(i);
                // ## 通过sheet得到当前共多少行
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

                List<MaintainMine> list = new ArrayList<>();
                // ## 循环所有的行,j从1开始,因为0为第一行,第一行为表头
                for (int j = 1; j < physicalNumberOfRows; j++) {
                    // ## 每循环一次,通过当前下标一行一行的获取
                    Row row = sheet.getRow(j);
                    MaintainMine maintainMine = new MaintainMine();
                    maintainMine.setMineName(row.getCell(0).getStringCellValue());
                    maintainMine.setMineGasGrade((int) row.getCell(1).getNumericCellValue());
                    maintainMine.setMineDesignPower(row.getCell(2).getNumericCellValue());
                    maintainMine.setMineProductionTime(row.getCell(3).getDateCellValue());
                    maintainMine.setMineReformPower(row.getCell(4).getNumericCellValue());
                    maintainMine.setMineReformTime(row.getCell(5).getDateCellValue());
                    maintainMine.setCollieryId((int)row.getCell(6).getNumericCellValue());
                    list.add(maintainMine);
                }
                // ## 批量添加
                dao.saveAll(list);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public MaintainMine queryById(Integer id) {
        return entityManager.find(MaintainMine.class,id);
    }
}
